#!/usr/bin/env python
# -*- coding: utf-8 -*-
from win32com.client import Dispatch
import win32com.client

class easyExcel:
      """A utility to make it easier to get at Excel.    Remembering
      to save the data is your problem, as is    error handling.
      Operates on one workbook at a time."""

      def __init__(self, filename=None):
          self.xlApp = win32com.client.Dispatch('Excel.Application')
          if filename:
              self.filename = filename
              self.xlBook = self.xlApp.Workbooks.Open(filename)
          else:
              self.xlBook = self.xlApp.Workbooks.Add()
              self.filename = ''  
      def save(self, newfilename=None):
          if newfilename:
              self.filename = newfilename
              self.xlBook.SaveAs(newfilename)
          else:
              self.xlBook.Save()    
      def close(self):
          self.xlBook.Close(SaveChanges=0)
          del self.xlApp

      def getCell(self, sheet, row, col):
          "Get value of one cell"
          sht = self.xlBook.Worksheets(sheet)
          return sht.Cells(row, col).Value

      def setCell(self, sheet, row, col, value):
          "set value of one cell"
          sht = self.xlBook.Worksheets(sheet)
          sht.Cells(row, col).Value = value
    
      def getRange(self, sheet, row1, col1, row2, col2):
          "return a 2d array (i.e. tuple of tuples)"
          sht = self.xlBook.Worksheets(sheet)
          return sht.Range(sht.Cells(row1, col1), sht.Cells(row2, col2)).Value

      def addPicture(self, sheet, pictureName, Left, Top, Width, Height):
          "Insert a picture in sheet"
          sht = self.xlBook.Worksheets(sheet)
          sht.Shapes.AddPicture(pictureName, 1, 1, Left, Top, Width, Height)
  
      def cpSheet(self, before):
          "copy sheet"
          shts = self.xlBook.Worksheets
          shts(1).Copy(None,shts(1))

      #列号一定要输入正常，要不我现在可处理不了报错哦
      #只能应用于投诉处理的表格
      def searchColum(self,sheet,colum,target):
            list_caseid = self.getRange(sheet,1,colum,2048,colum)
            for i in range(1,2048):
                  if list_caseid[i][0] and str(list_caseid[i][0]).find(target) != -1 :
                   #    成功匹配返回行号，excel中的行号是
                        return i 
            #匹配失败后返回 -1
            return -1
      #找到最后一个可以插入编辑的行
      #注意，返回的的不是excel中的行号，而是程序的行号
      def find_last_row(self,sheet,colum):
            list_caseid = self.getRange(sheet,1,colum,2048,colum)
            for i in range(1,2048) :
                  if not list_caseid[i][0] :
                        return i

      def find_last_colum(self,sheet,row):
            lines      = self.getRange(sheet,row,1,row,2048)
            for i in range(1,2048) :
                  if not lines[0][i] :
                        return i

if __name__ == '__main__' :
      
      x = easyExcel("D:\Emails\PayPalComplain.xlsx")
      """
      pp_caseid ='PP-001-380-675-236'
      sheet2 = "ProcessChat"
      transaction_info ={'transaction_no': '4EL05309XE4001819',
                         'currency': 'USD',
                         'amount': '57.96',
                         'case_id': 'PP-001-380-675-236',
                         'date': '2011-07-28',
                         'invoice_no': 'en-uk_1106170221_125',
                         'subject': 'PayPal\xe4\xb9\xb0\xe5\xae\xb6\xe6\x8a\x95\xe8\xaf\x89\xe5\xb7\xb2\xe8\xa7\xa3\xe5\x86\xb3PP-001-380-675-236'}
      search_m = x.searchColum(sheet2,1,transaction_info['case_id'])

      n = x.searchColum('InfoRequest',3,pp_caseid)
      print n            
      """
      m = x.find_last_row('InfoRequest',3)
      print m
      
      #k = x.find_last_colum('InfoRequest',510)
      #print k

      #lines  = x.getRange('InfoRequest',510,1,510,8)
      #print lines[0]
      x.save()
      x.close()
